Mysql 优化 (一)

优化的方面

存储层：数据表”存储引擎”选取、字段类型选取、逆范式(3范式)
设计层：索引、分区/分表
架构层：分布式部署(集群)
sql语句层：结果一样的情况下，要选择效率高、速度快、节省资源的sql语句执行

存储引擎

查看引擎：show engines;
查看表结构：show create table table_name;
查看表状态：show table status like ‘table_name’;

什么是存储引擎
MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

MYISAM

每个MyISAM在磁盘上存储成三个文件。

.frm : 表结构文件
.MYD : 数据文件(mysql data)
.MYI : 索引文件(mysql index)

管理非事务表，提供高速存储和检索，以及全文搜索能力。(支持物理复制、粘贴以实现数据的备份、还原操作)

数据写入顺序

数据写入时候，不会按照主键id值给予排序存储，该特点导致数据写入的速度非常快。
写没有固定顺序，存也按照写的顺序存，不给考虑先后顺序。

并发性

mysiam的并发性(同时处理工作的能力)较比innodb要稍逊色
因为数据表是“表锁”(innodb行锁)

压缩

如果一个myisam数据表存储的数据非常多，就会占据很大的硬盘空间，硬盘空间我们不在乎，在乎的是mysql数据库系统为了处理该数据表就需要分配更多的资源，为了节省资源，可以把这个myisam数据表给进行压缩处理。

1
2
3

[root@localhost ~] pwd
/usr/bin/myisampack
压缩语法：myisampack 表名(绝对路径定义)

压缩后的数据表仍然可以支持查询操作,压缩后的数据表需要根据最新的数据位置把索引重新建立一次。
根据压缩后的据把索引重建建立起来。

1
2
3

[root@localhost ~] find / -name myisamchk
/usr/bin/myisamchk
重建索引语法：myisamchk  -rq  表名(绝对路径定义)

压缩的数据表不能再写入数据了(尝试写入会造成数据表崩溃)，必须解压后才可以。
(解压缩的同时，索引会自动重建)

1	解压语法：myisamchk --unpack 表名(绝对路径定义)

innodb

.frm : 结构文件
.ibd : 数据/索引文件

innodb数据表不能直接的复制/粘贴文件，以便做进行备份还原，可以通过如下指令完成数据的备份和还原：
存储引擎用于事务处理应用程序，具有众多特性，包括ACID事务支持，提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

1 2	mysqldump -uroot -p密码数据库名字 > /home/xiaogang/xxx.sql [备份] mysql -uroot -p密码数据库名字 < /home/xiaogang/xxx.sql [还原]

数据写入顺序

该innodb数据表，数据的写入顺序与存储的顺序不一致，需要按照主键的顺序把记录摆放到对应的位置上去，速度比Myisam的要稍慢。

并发性

并发性高，多人同时请求，速度快、效率高。
innodb锁机制：行锁，每次只锁住一条记录信息。

myisam , innodb 的取舍

myisam：写入数据非常快，适合使用场合dedecms/phpcms/discuz/微博系统等写入、读取操作多的系统。
innodb：适合业务逻辑比较强的系统，修改操作较多的，例如ecshop、crm、办公系统、商城系统
myisam：类型不支持事务处理等高级处理，而InnoDB类型支持.
myisam：类型的表强调的是性能，其执行数度比InnoDB类型更快.
myisam：支持表锁，InnoDB支持行锁。
innodb：不支持FULLTEXT类型的索引.
innodb：中不保存表的具体行数，执行select count(*) from table时，InnoDB要扫描一整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可.

memory

Memory使用哈希索引，所以数据的存取速度非常快。

内存存储引擎

特点：内部数据都运行在内存中，可以应用于临时表中在需要快速查找引用和其他类似数据的环境下，数据存储也在内存中，速度非常快，临时存储一些信息
缺点：服务器如果断电，就会清空该存储引擎的全部数据

存储引擎的对比

特点	MyISAM	Memory	InnoDB
存储限制	没有	有	64TB
事务安全	MyISAM		支持
锁机制	表锁	表锁	行锁
B树索引	支持	支持	支持
哈希索引		支持	支持
全文索引	支持
集群索引			支持外键
数据可压缩	支持
空间使用	低	N/A	高
内存使用	低	中等	高
批量插入的速度	高	高	低
支持外键			支持

三范式

设置索引

alter table student5 add primary key (id);
alter table student5 add unique  key  (name);
alter table student5 add         key (height);
alter table student5 add fulltext key (introduce);

删除索引

语法：
alter table 表名 drop key 索引名称; //[非主键]索引删除(唯一/普通/全文/复合)
alter table 表名 drop primary key; //[主键]索引删除

注意：删除主键索引，必须先去除auto_increment属性

使用以下命令可查看是否开启单独存储数据与索引文件：
* show variables like '%innodb_file_per%';
可以修改配置，改变innodb数据表的数据、索引文件的存储方式：
* set global innodb_file_per_table=1/0;

InnodB数据表数据、索引合并文件分为两种情况：
* 每个数据表都有自己的数据、索引合并文件
* 数据库中全部的数据表共享一个数据、索引合并文件